"""
Case Type   : DML
Case Name   : 验证for share与for share无阻塞
Create At   : 2024/10/29
Owner       : nanyang12
Description :
        1.建表，插入数据
        2.会话1 在step2中每个事务未commit时执行for share;
        3.会话2 开启事务提交 执行for share;
        4.执行commit并清理环境
Expect      :
        1.成功
        2.成功 不阻塞
        3.成功 不阻塞
        4.清理环境完成
History     :
"""
import os
import unittest
import sys

from testcase.utils.Common import Common
from testcase.utils.ComThread import ComThread
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro

LOG = Logger()
commonsh = CommonSH('dbuser')


class Temporarytable(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.constant = Constant()
        self.DB_ENV_PATH = macro.DB_ENV_PATH
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.user_node = Node('PrimaryDbUser')
        self.pri_root_node = Node('PrimaryRoot')
        self.database = self.user_node.db_name
        self.com = Common()
        text = '---Opengauss_Function_DML_Lock_Case0125 start---'
        self.log.info(text)

    def test_temporary_table(self):
        step1 = '--step1:建表，插入数据; expect:成功--'
        LOG.info(step1)
        sql = f'''drop table if exists t_lock_0125_01 cascade;
                  drop table if exists t_lock_0125_02 cascade;
                  create table t_lock_0125_01(c_int1 int primary key, c_int2 int);
                  create table t_lock_0125_02(c_int3 int , c_int4 int);
                  insert into t_lock_0125_01 values (generate_series(1,20), generate_series(1,20));
                  alter table t_lock_0125_02 add foreign key (c_int3) references t_lock_0125_01 (c_int1);
              '''
        sql_cmd = self.pri_sh.execut_db_sql(sql)
        LOG.info(sql_cmd)
        self.assertNotIn("ERROR", sql_cmd, step1)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_cmd)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, sql_cmd)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_cmd)

        step2 = ('''--step2:在会话1中每个事务未commit时执行for share,成功--
                    --step3:同时在会话2 开启事务执行for share并提交,成功--
                 ''')

        LOG.info(step2)
        sql1 = '''start transaction;
                  select c_int2 from t_lock_0125_01 where c_int1 = 3 for share;
                  select c_int1,c_int2 from t_lock_0125_01 where c_int1 = 3 for share;
                  select mode from pg_lock_status() where relation in (select oid from pg_class 
                  where relname='t_lock_0125_01');
                  select mode from pg_locks where relation in (select oid from pg_class 
                  where relname='t_lock_0125_01');
                  select mode from dbe_perf.locks where relation in (select oid from pg_class 
                  where relname='t_lock_0125_01');
                  select mode from dbe_perf.global_locks where relation in (select oid from pg_class 
                  where relname='t_lock_0125_01');
                  commit;
               '''
        sql2 = '''start transaction;
                  select c_int2 from t_lock_0125_01 where c_int1 = 3 for share;
                  select c_int1,c_int2 from t_lock_0125_01 where c_int1 = 3 for share;
                  select mode from pg_lock_status() where relation in (select oid from pg_class 
                  where relname='t_lock_0125_01');
                  select mode from pg_locks where relation in (select oid from pg_class 
                  where relname='t_lock_0125_01');
                  select mode from dbe_perf.locks where relation in (select oid from pg_class 
                  where relname='t_lock_0125_01');
                  select mode from dbe_perf.global_locks where relation in (select oid from pg_class 
                  where relname='t_lock_0125_01');
                  commit;
               '''
        sql_list = [sql1, sql2]
        thread_list = []
        for sql in sql_list:
            self.log.info(sql)
            test_thread = ComThread(self.pri_sh.execut_db_sql,
                                    args=(sql,))
            test_thread.setDaemon(True)
            thread_list.append(test_thread)

        self.log.info("---启动会话1、会话2的线程------")
        for thread in thread_list:
            thread.start()

        self.log.info("线程执行结果")
        for thread in thread_list:
            thread.join(30)
            result = thread.get_result()
            self.log.info('result:' + str(thread_list.index(thread)))
            self.log.info(result)
            self.assertNotIn('ERROR', result, '执行失败: ' + "线程执行失败")
            self.assertIn('RowShareLock', result, '执行失败: ' + "线程执行失败")

    def tearDown(self):
        step4 = '--step4:清理环境，参数恢复默认值--'
        LOG.info(step4)
        sql = f'''drop table if exists t_lock_0125_01 cascade;
                  drop table if exists t_lock_0125_02 cascade;
              '''
        sql_cmd = self.pri_sh.execut_db_sql(sql)
        LOG.info(sql_cmd)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, sql_cmd)
        text = '---Opengauss_Function_DML_Lock_Case0125 finish---'
        self.log.info(text)
